import pymysql
import pandas as pd
import numpy as np
from sklearn.metrics.pairwise import pairwise_distances
print()
def sqlToJson(cursor,sql,key):
    cursor.execute(sql)
    data=cursor.fetchall()
    jsonList=[]
    for i in data:
        jsonList.append(dict(zip(key,i)))
    return jsonList

def connectDB():
    conn = pymysql.connect(host='119.23.218.131',
                           port=3306, user='root',
                           passwd='cnjson!@#456',
                           db="fasteat_ucenter",
                           charset='utf8',
                           cursorclass=pymysql.cursors.DictCursor)  #
    return conn
conn = connectDB()  # "oceantest"
with conn.cursor() as cursor:
  sql = "select user_id,menu_id from t_ucenter_history"  #
  search_count = cursor.execute(sql)
  result = cursor.fetchall()
real_user={}
for dict_user_id in result :
  user_id_key=dict_user_id['user_id']
  if user_id_key not in real_user.keys():
    real_user[user_id_key]=[]
    real_user[user_id_key].append(dict_user_id['menu_id'])
  else:
    real_user[user_id_key].append(dict_user_id['menu_id']) 
user_id_list=list(real_user.keys())
real_menu={}
for dict_user_id in result:
  menu_id_key=dict_user_id['menu_id']
  if menu_id_key not in real_menu.keys():
    real_menu[menu_id_key]=[]
    real_menu[menu_id_key].append(dict_user_id['user_id'])
  else:
    real_user[user_id_key].append(dict_user_id['menu_id'])
menu_id_list=list(real_menu.keys())
pf=pd.DataFrame(columns=menu_id_list,index=user_id_list)
for user_id in user_id_list:
  for menu_id in real_user[user_id]:
    pf.loc[user_id,menu_id]=1#pf为真正的用户-菜谱的dataframe
pf=pf.fillna(0)
conn = connectDB()  # "oceantest"
with conn.cursor() as cursor:
  sql = "select attention_id,user_id from t_ucenter_subscription"
  search_count = cursor.execute(sql)
  result = cursor.fetchall()
real_attention={}
for dict_attention_id in result :
  user_id_key=dict_attention_id['user_id']
  if user_id_key not in real_attention.keys():
    real_attention[user_id_key]=[]
    real_attention[user_id_key].append(dict_attention_id['attention_id'])
  else:
    real_attention[user_id_key].append(dict_attention_id['attention_id']) 
attention_user_id_list=list(real_attention.keys())#user_id
real_attention={}
for dict_attention_id in result:
  attention_id_key=dict_attention_id['attention_id']
  if attention_id_key not in real_attention.keys():
    real_attention[attention_id_key]=[]
    real_attention[attention_id_key].append(dict_attention_id['user_id'])
  else:
    real_attention[attention_id_key].append(dict_attention_id['user_id'])
attention_id_list=list(real_attention.keys())
df=pd.DataFrame(columns=attention_user_id_list,index=attention_id_list)
for attention_id in attention_id_list:
  for user_id in real_attention:
    df.loc[attention_id,user_id]=1#df为真正的关注用户的dataframe
df=df.fillna(0)
user_similar=1-pairwise_distances(pf.values,metric="jaccard")
user_similar=pd.DataFrame(user_similar,columns=user_id_list,index=user_id_list)
topN_users={}
def tuijian():
  for i in user_similar.index:
   _df=user_similar.loc[i].drop([i])
   _df_sorted=_df.sort_values(ascending=False)
   top2=list(_df_sorted.index[:45])
   topN_users[i]=top2
  rs_results={}
  for user,sim_users in topN_users.items():
   rs_result=set()
   for sim_user in sim_users:
    rs_result=rs_result.union(set(pf.loc[sim_user,:].replace(0,np.nan).dropna().index))
    rs_result-= set(pf.loc[user,:].replace(0,np.nan).dropna().index)
    rs_results[user]=rs_result
  for i in df.index:#将自己关注自己的情况丢弃
      df.loc[i,i]=0
  focus_users={}#建立关注人的字典
  for i in df.index:
      focus_user=list(df.loc[i,:].replace(0,np.nan).dropna().index)
      focus_users[i]=focus_user
  #将被关注人的浏览菜谱存储在foucs_menus中
  focus_menus={}
  for i,focus_user_list in focus_users.items():#i是关注他人的人，focus_user是被关注的人
      focus_menu=set()
      for focus_user in focus_user_list:
          focus_menu=focus_menu.union(set(df.loc[focus_user,:].replace(0,np.nan).dropna().index))
          if i in user_id_list:
            focus_menu-=set(pf.loc[i,:].replace(0,np.nan).dropna().index)
          focus_menus[i]=focus_menu
  real_results={}
  for i in pf.index:
    if i in rs_results.keys() and i in focus_menus.keys():
      real_results[i]=set(rs_results[i].union(focus_menus[i]))
    elif i in focus_menus.keys():
      real_results[i]=focus_menus[i]
    else:
      real_results[i]=rs_results[i]
  return real_results

print(tuijian())




